#!/usr/bin/perl -w
use strict;
use DBI;
use lib "/home/x/Code/WEB_PHASTREGEX";
use UCSChooks;
# Jason Weirather 2010.
# This file will use a completed conservation scan and print the hits.
# By default it will not filter results based on 

if(scalar(@ARGV) < 1) { die "at least tell it the regex_id.\noptionally give it a true for filtering and it will read a filtering file as standard input.\n"; }
my $regex_id = shift @ARGV;
my $restrict_conservation_output = 'false';
if(scalar(@ARGV) > 0) { 
  $restrict_conservation_output = shift @ARGV;
}

### optionally you can have filtering conditions.
my $conservation_output_database = '';
my $conservation_output_score = '';
if($restrict_conservation_output eq 'true') { 
  chomp(my $line = <STDIN>);
  ($conservation_output_database,$conservation_output_score) = split(/\t/,$line);
}
### format for filtering conditon is the database and conservation tab delimited.
### example.  for human:
### _vertebrate	04_05

# would require it to be conserved in vertebrates at at least 0.4 to 0.5 phastcon

my $username = 'x';
my $tablebasename = '';
#automatically set tablebasename;
my $zdatabase = "zebraphish";
my $platform = "mysql";
my $user = $username;
my $zdbh = DBI->connect("dbi:$platform:$zdatabase",$user,'');
my $zquery = "SELECT completed, species FROM regex_scan WHERE db_id = $regex_id LIMIT 1";
#print "$zquery\n";
my $zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
$zst->execute() or die "The execution of query field: $DBI::errstr";
my $count=0;
my $species;
while(my $row = $zst->fetchrow_hashref()) {
  $count++;
  if($row->{'completed'} == 0) { die "the regex search for this sequence is incomplete.\n"; }
  $species = $row->{'species'};
}
if($count == 0) { die "nothing to look at\n"; }
# if we are still here, then we haven't finished this yet.
$zquery = "SELECT completed FROM conservation_scan WHERE regex_id = $regex_id";
$zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
$zst->execute() or die "The execution of query field: $DBI::errstr";
$count = 0;
while(my $row = $zst->fetchrow_hashref()) {
  $count++;
  if($row->{'completed'} == 0) { die "conservation scan is incomplete.\n"; }
}
if($count == 0) { die "hasn't been scanned yet.\n"; }

## now we have verified that the regular expression scan was done and the conservation can was done

$zquery = "SELECT species, conservation_database, conservation_score FROM conservation_scan JOIN hit_conservation ON conservation_scan.db_id = conservation_scan_id AND regex_id = '$regex_id' GROUP BY conservation_database, conservation_score ORDER BY conservation_database, conservation_score";
#print "$zquery\n";
$zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
$zst->execute() or die "The execution of query field: $DBI::errstr";
#my $species;
my @conservation_fields;
my %hash_of_hit_hashes;
while(my $row = $zst->fetchrow_hashref()) {
  #$species = $row->{'species'};
  my $line = $row->{'conservation_database'} . "\t" . $row->{'conservation_score'};
  push @conservation_fields, $line;
  $hash_of_hit_hashes{$line} = 1;
}

foreach my $conservation_field (@conservation_fields) {
  my %hits;
  my ($conservation_database,$conservation_score) = split(/\t/,$conservation_field);
  print "###conservation_field-> 1 if present for database: $conservation_database score: $conservation_score\n";
  $zquery = "SELECT hit_id FROM hit_conservation JOIN conservation_scan ON conservation_scan_id = conservation_scan.db_id AND conservation_score = '$conservation_score' AND conservation_database = '$conservation_database' AND regex_id = $regex_id";
  #print "$zquery\n";
  $zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
  $zst->execute() or die "The execution of query field: $DBI::errstr";
  my $hit_id;
  while(my $row = $zst->fetchrow_hashref()) {
    $hit_id = $row->{'hit_id'};
    $hits{$hit_id} = 1;
  }
  $hash_of_hit_hashes{$conservation_field} = \%hits;
  # these a
}

### now get stuff to print
$zquery = "SELECT hits.db_id, chromosome, hit_start, hit_finish, sequence FROM hits JOIN chromosome_scan ON chromosome_scan_id = chromosome_scan.db_id JOIN regex_scan ON regex_scan.db_id = regex_id AND regex_id = $regex_id ORDER BY chromosome, hit_start, hits.db_id";
#die "$zquery\n";
$zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
$zst->execute() or die "The execution of query field: $DBI::errstr";
#my %output_hits;
while(my $row = $zst->fetchrow_hashref()) {
  my $hit_id = $row->{'db_id'};
  my $chromosome = $row->{'chromosome'};
  my $hit_start = $row->{'hit_start'};
  my $hit_finish = $row->{'hit_finish'};
  my $sequence = $row->{'sequence'};
  my $hiturl = UCSChooks->make_excel_link($species,$chromosome,$hit_start,$hit_finish);
  my $output_line = "$chromosome\t$hit_start\t$hit_finish\t$sequence\t$hiturl\t";
  my $do_output = 'false';
  if($restrict_conservation_output ne 'true') { $do_output = 'true'; }
  if(exists($hash_of_hit_hashes{"$conservation_output_database\t$conservation_output_score"})) {
    if(exists($hash_of_hit_hashes{"$conservation_output_database\t$conservation_output_score"}->{$hit_id})) {
      $do_output = 'true';    
    }
  }
  foreach my $conservation_field (@conservation_fields) {
    if(exists($hash_of_hit_hashes{$conservation_field}->{$hit_id})) { $output_line .= "1\t"; }
    else { $output_line .= "\t"; }
  }
  if($do_output eq 'true') {
    ### do the output here
    chop($output_line);
    print "$output_line\n";
  }
}
